from syncMysqlData.sqlutils import SyncSqlConnTool


class SyncDataUtils(object):

    def __init__(self):
        self.__is_table_exist = False  # 表默认不存在
        self.__syncSqlTool = SyncSqlConnTool()
        self.__table_name = "tyc_company_list_tianjin"

    # 同步数据到服务器
    def sync_detail_to_server(self):
        try:
            # 获取会话指针
            with self.__syncSqlTool.local_conn.cursor() as cursor:
                # 查询sql语句
                sql = "select * from %s" % (self.__table_name)  # 此处tyc_company_list_tianjin需要修改为对应的表
                count = cursor.execute(sql)
                print(count)
                # 提交
                self.__syncSqlTool.local_conn.commit()

                while True:
                    result = cursor.fetchone()
                    if not result:
                        break

                    print(result["company_id"])
                    print(result["company_name"])
                    print(result["company_url"])
                    self.__write_data_to_server(result["company_id"], result["company_name"], result["company_url"])

        finally:
            self.__syncSqlTool.local_conn.close()
            self.__syncSqlTool.server_conn.close()

    # 保存数据到服务器
    def __write_data_to_server(self, company_id, company_name, company_url):
        # try:
            self.__create_server_table_if_not_exist()
            sqlstr = "REPLACE INTO %s (company_id,company_name,company_url) VALUES ('%d','%s','%s')" % (
                self.__table_name, company_id, company_name, company_url)
            print("__write_data_to_server : " + sqlstr)
            self.__syncSqlTool.server_cursor.execute(sqlstr)
            self.__syncSqlTool.server_conn.commit()
            print("s% 数据库写入成功!!!", company_name)
        # except Exception as e:
        #     print(e)

    # 如果不存在，则创建服务器的表
    def __create_server_table_if_not_exist(self):
        if not self.__is_table_exist:
            self.__is_table_exist = True

            # 如果不存在，则创建数据表SQL语句
            sql = """CREATE TABLE IF NOT Exists %s (
                         company_id BIGINT NOT NULL PRIMARY KEY,
                         company_name  VARCHAR(64) NOT NULL,
                         company_url VARCHAR(100) )""" % (self.__table_name)
            print("__create_server_table_if_not_exist : " + sql)
            self.__syncSqlTool.server_cursor.execute(sql)

    # 同步服务器的数据到本地
    def sync_detail_to_local(self):
        try:
            # 获取会话指针
            with self.__syncSqlTool.local_conn.cursor() as cursor:
                # 查询sql语句
                sql = "select * from %s" % (self.__table_name)  # 此处tyc_company_list_tianjin需要修改为对应的表
                count = cursor.execute(sql)
                print(count)
                # 提交
                self.__syncSqlTool.local_conn.commit()

                while True:
                    result = cursor.fetchone()
                    if not result:
                        break

                    print(result["company_id"])
                    print(result["company_name"])
                    print(result["company_url"])
                    self.__write_data_to_local(result["company_id"], result["company_name"], result["company_url"])

        finally:
            self.__syncSqlTool.local_conn.close()
            self.__syncSqlTool.server_conn.close()

    # 保存数据到本地
    def __write_data_to_local(self, company_id, company_name, company_url):
        try:
            self.__create_local_table_if_not_exist()
            sqlstr = "REPLACE INTO %s (company_id,company_name,company_url) VALUES ('%d','%s','%s')" % (
                self.__table_name, company_id, company_name, company_url)
            print("__write_data_to_local : " + sqlstr)
            self.__syncSqlTool.local_cursor.execute(sqlstr)
            self.__syncSqlTool.local_conn.commit()
            print("s% 数据库写入成功!!!", company_name)
        except:
            pass

    # 如果不存在，则创建本地的表
    def __create_local_table_if_not_exist(self):
        if not self.__is_table_exist:
            self.__is_table_exist = True

            # 如果不存在，则创建数据表SQL语句
            sql = """CREATE TABLE IF NOT Exists %s (
                         company_id BIGINT NOT NULL PRIMARY KEY,
                         company_name  VARCHAR(64) NOT NULL,
                         company_url VARCHAR(100) )""" % (self.__table_name)
            print("__create_local_table_if_not_exist : " + sql)
            self.__syncSqlTool.local_cursor.execute(sql)


if __name__ == '__main__':
    syncfun = SyncDataUtils()
    syncfun.sync_detail_to_server()
